-- Drop Existing Procedures

IF OBJECT_ID(N'[dbo].[p_ACTP_EmployeeCertification_Insert]') IS NOT NULL
	DROP PROCEDURE [dbo].[p_ACTP_EmployeeCertification_Insert]

IF OBJECT_ID(N'[dbo].[p_ACTP_EmployeeCertification_Update]') IS NOT NULL
	DROP PROCEDURE [dbo].[p_ACTP_EmployeeCertification_Update]

IF OBJECT_ID(N'[dbo].[p_ACTP_EmployeeCertification_InsertUpdate]') IS NOT NULL
	DROP PROCEDURE [dbo].[p_ACTP_EmployeeCertification_InsertUpdate]

IF OBJECT_ID(N'[dbo].[p_ACTP_EmployeeCertification_Delete]') IS NOT NULL
	DROP PROCEDURE [dbo].[p_ACTP_EmployeeCertification_Delete]

IF OBJECT_ID(N'[dbo].[p_ACTP_EmployeeCertification_DeleteDynamic]') IS NOT NULL
	DROP PROCEDURE [dbo].[p_ACTP_EmployeeCertification_DeleteDynamic]

IF OBJECT_ID(N'[dbo].[p_ACTP_EmployeeCertification_Load]') IS NOT NULL
	DROP PROCEDURE [dbo].[p_ACTP_EmployeeCertification_Load]

IF OBJECT_ID(N'[dbo].[p_ACTP_EmployeeCertification_SelectDynamic]') IS NOT NULL
	DROP PROCEDURE [dbo].[p_ACTP_EmployeeCertification_SelectDynamic]

IF OBJECT_ID(N'[dbo].[p_ACTP_EmployeeCertification_SelectAll]') IS NOT NULL
	DROP PROCEDURE [dbo].[p_ACTP_EmployeeCertification_SelectAll]

IF OBJECT_ID(N'[dbo].[p_ACTP_EmployeeCertification_SelectBy_CertificationID]') IS NOT NULL
	DROP PROCEDURE [dbo].[p_ACTP_EmployeeCertification_SelectBy_CertificationID]

IF OBJECT_ID(N'[dbo].[p_ACTP_EmployeeCertification_SelectBy_EmployeeID]') IS NOT NULL
	DROP PROCEDURE [dbo].[p_ACTP_EmployeeCertification_SelectBy_EmployeeID]

IF OBJECT_ID(N'[dbo].[p_ACTP_EmployeeCertification_DeleteBy_CertificationID]') IS NOT NULL
	DROP PROCEDURE [dbo].[p_ACTP_EmployeeCertification_DeleteBy_CertificationID]

IF OBJECT_ID(N'[dbo].[p_ACTP_EmployeeCertification_DeleteBy_EmployeeID]') IS NOT NULL
	DROP PROCEDURE [dbo].[p_ACTP_EmployeeCertification_DeleteBy_EmployeeID]


GO


------------------------------------------------------------------------------------------------------------------------
-- Stored procedure name: [dbo].[p_ACTP_EmployeeCertification_Insert]
-- Database: ASPNETVN.PORTAL
-- Author: Ngo Thanh Tung
-- Time created: Monday, October 04, 2010
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[p_ACTP_EmployeeCertification_Insert]
	@ModuleID uniqueidentifier,
	@CertificationID int,
	@EmployeeID int,
	@CertifiedDate datetime,
	@ExpiredDate datetime,
	@CreatedBy varchar(128),
	@CreatedDate datetime,
	@UpdatedBy varchar(128),
	@UpdatedDate datetime,
	@ID int OUTPUT
AS

INSERT INTO [dbo].[t_ACTP_EmployeeCertifications]
(
	[ModuleID],
	[CertificationID],
	[EmployeeID],
	[CertifiedDate],
	[ExpiredDate],
	[CreatedBy],
	[CreatedDate],
	[UpdatedBy],
	[UpdatedDate]
)
VALUES 
(
	@ModuleID,
	@CertificationID,
	@EmployeeID,
	@CertifiedDate,
	@ExpiredDate,
	@CreatedBy,
	@CreatedDate,
	@UpdatedBy,
	@UpdatedDate
)

SET @ID = SCOPE_IDENTITY()

GO

------------------------------------------------------------------------------------------------------------------------
-- Stored procedure name: [dbo].[p_ACTP_EmployeeCertification_Update]
-- Database: ASPNETVN.PORTAL
-- Author: Ngo Thanh Tung
-- Time created: Monday, October 04, 2010
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[p_ACTP_EmployeeCertification_Update]
	@ID int,
	@ModuleID uniqueidentifier,
	@CertificationID int,
	@EmployeeID int,
	@CertifiedDate datetime,
	@ExpiredDate datetime,
	@CreatedBy varchar(128),
	@CreatedDate datetime,
	@UpdatedBy varchar(128),
	@UpdatedDate datetime
AS

UPDATE
	[dbo].[t_ACTP_EmployeeCertifications]
SET
	[ModuleID] = @ModuleID,
	[CertificationID] = @CertificationID,
	[EmployeeID] = @EmployeeID,
	[CertifiedDate] = @CertifiedDate,
	[ExpiredDate] = @ExpiredDate,
	[CreatedBy] = @CreatedBy,
	[CreatedDate] = @CreatedDate,
	[UpdatedBy] = @UpdatedBy,
	[UpdatedDate] = @UpdatedDate
WHERE
	[ID] = @ID

GO

------------------------------------------------------------------------------------------------------------------------
-- Stored procedure name: [dbo].[p_ACTP_EmployeeCertification_InsertUpdate]
-- Database: ASPNETVN.PORTAL
-- Author: Ngo Thanh Tung
-- Time created: Monday, October 04, 2010
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[p_ACTP_EmployeeCertification_InsertUpdate]
	@ID int,
	@ModuleID uniqueidentifier,
	@CertificationID int,
	@EmployeeID int,
	@CertifiedDate datetime,
	@ExpiredDate datetime,
	@CreatedBy varchar(128),
	@CreatedDate datetime,
	@UpdatedBy varchar(128),
	@UpdatedDate datetime
AS
IF EXISTS(SELECT [ID] FROM [dbo].[t_ACTP_EmployeeCertifications] WHERE [ID] = @ID)
	BEGIN
		UPDATE
			[dbo].[t_ACTP_EmployeeCertifications] 
		SET
			[ModuleID] = @ModuleID,
			[CertificationID] = @CertificationID,
			[EmployeeID] = @EmployeeID,
			[CertifiedDate] = @CertifiedDate,
			[ExpiredDate] = @ExpiredDate,
			[CreatedBy] = @CreatedBy,
			[CreatedDate] = @CreatedDate,
			[UpdatedBy] = @UpdatedBy,
			[UpdatedDate] = @UpdatedDate
		WHERE
			[ID] = @ID
	END
ELSE
	BEGIN
		
		INSERT INTO [dbo].[t_ACTP_EmployeeCertifications]
		(
			[ModuleID],
			[CertificationID],
			[EmployeeID],
			[CertifiedDate],
			[ExpiredDate],
			[CreatedBy],
			[CreatedDate],
			[UpdatedBy],
			[UpdatedDate]
		)
		VALUES 
		(
			@ModuleID,
			@CertificationID,
			@EmployeeID,
			@CertifiedDate,
			@ExpiredDate,
			@CreatedBy,
			@CreatedDate,
			@UpdatedBy,
			@UpdatedDate
		)		
	END
GO

------------------------------------------------------------------------------------------------------------------------
-- Stored procedure name: [dbo].[p_ACTP_EmployeeCertification_Delete]
-- Database: ASPNETVN.PORTAL
-- Author: Ngo Thanh Tung
-- Time created: Monday, October 04, 2010
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[p_ACTP_EmployeeCertification_Delete]
	@ID int
AS

DELETE FROM 
	[dbo].[t_ACTP_EmployeeCertifications]
WHERE
	[ID] = @ID

GO

------------------------------------------------------------------------------------------------------------------------
-- Stored procedure name: [dbo].[p_ACTP_EmployeeCertification_DeleteBy_CertificationID]
-- Database: ASPNETVN.PORTAL
-- Author: Ngo Thanh Tung
-- Time created: Monday, October 04, 2010
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[p_ACTP_EmployeeCertification_DeleteBy_CertificationID]
	@CertificationID int
AS

DELETE FROM [dbo].[t_ACTP_EmployeeCertifications]
WHERE
	[CertificationID] = @CertificationID

GO

GO

------------------------------------------------------------------------------------------------------------------------
-- Stored procedure name: [dbo].[p_ACTP_EmployeeCertification_DeleteBy_EmployeeID]
-- Database: ASPNETVN.PORTAL
-- Author: Ngo Thanh Tung
-- Time created: Monday, October 04, 2010
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[p_ACTP_EmployeeCertification_DeleteBy_EmployeeID]
	@EmployeeID int
AS

DELETE FROM [dbo].[t_ACTP_EmployeeCertifications]
WHERE
	[EmployeeID] = @EmployeeID

GO

GO

------------------------------------------------------------------------------------------------------------------------
-- Stored procedure name: [dbo].[p_ACTP_EmployeeCertification_DeleteDynamic]
-- Database: ASPNETVN.PORTAL
-- Author: Ngo Thanh Tung
-- Time created: Monday, October 04, 2010
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[p_ACTP_EmployeeCertification_DeleteDynamic]
	@WhereCondition NVARCHAR(500)
AS

DECLARE @SQL NVARCHAR(MAX)

SET @SQL = 'DELETE FROM [dbo].[t_ACTP_EmployeeCertifications] WHERE ' + @WhereCondition

EXEC sp_executesql @SQL

GO

------------------------------------------------------------------------------------------------------------------------
-- Stored procedure name: [dbo].[p_ACTP_EmployeeCertification_Load]
-- Database: ASPNETVN.PORTAL
-- Author: Ngo Thanh Tung
-- Time created: Monday, October 04, 2010
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[p_ACTP_EmployeeCertification_Load]
	@ID int
AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SELECT
	[ID],
	[ModuleID],
	[CertificationID],
	[EmployeeID],
	[CertifiedDate],
	[ExpiredDate],
	[CreatedBy],
	[CreatedDate],
	[UpdatedBy],
	[UpdatedDate]
FROM
	[dbo].[t_ACTP_EmployeeCertifications]
WHERE
	[ID] = @ID
GO

------------------------------------------------------------------------------------------------------------------------
-- Stored procedure name: [dbo].[p_ACTP_EmployeeCertification_SelectBy_CertificationID]
-- Database: ASPNETVN.PORTAL
-- Author: Ngo Thanh Tung
-- Time created: Monday, October 04, 2010
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[p_ACTP_EmployeeCertification_SelectBy_CertificationID]
	@CertificationID int
AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SELECT
	[ID],
	[ModuleID],
	[CertificationID],
	[EmployeeID],
	[CertifiedDate],
	[ExpiredDate],
	[CreatedBy],
	[CreatedDate],
	[UpdatedBy],
	[UpdatedDate]
FROM
	[dbo].[t_ACTP_EmployeeCertifications]
WHERE
	[CertificationID] = @CertificationID

GO

------------------------------------------------------------------------------------------------------------------------
-- Stored procedure name: [dbo].[p_ACTP_EmployeeCertification_SelectBy_EmployeeID]
-- Database: ASPNETVN.PORTAL
-- Author: Ngo Thanh Tung
-- Time created: Monday, October 04, 2010
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[p_ACTP_EmployeeCertification_SelectBy_EmployeeID]
	@EmployeeID int
AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SELECT
	[ID],
	[ModuleID],
	[CertificationID],
	[EmployeeID],
	[CertifiedDate],
	[ExpiredDate],
	[CreatedBy],
	[CreatedDate],
	[UpdatedBy],
	[UpdatedDate]
FROM
	[dbo].[t_ACTP_EmployeeCertifications]
WHERE
	[EmployeeID] = @EmployeeID

GO

------------------------------------------------------------------------------------------------------------------------
-- Stored procedure name: [dbo].[p_ACTP_EmployeeCertification_SelectDynamic]
-- Database: ASPNETVN.PORTAL
-- Author: Ngo Thanh Tung
-- Time created: Monday, October 04, 2010
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[p_ACTP_EmployeeCertification_SelectDynamic]
	@WhereCondition NVARCHAR(500),
	@OrderByExpression NVARCHAR(250) = NULL
AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

DECLARE @SQL NVARCHAR(MAX)

SET @SQL = 
'SELECT 
	[ID],
	[ModuleID],
	[CertificationID],
	[EmployeeID],
	[CertifiedDate],
	[ExpiredDate],
	[CreatedBy],
	[CreatedDate],
	[UpdatedBy],
	[UpdatedDate]
FROM [dbo].[t_ACTP_EmployeeCertifications] 
WHERE ' + @WhereCondition

IF @OrderByExpression IS NOT NULL AND LEN(@OrderByExpression) > 0
BEGIN
	SET @SQL = @SQL + ' ORDER BY ' + @OrderByExpression
END

EXEC sp_executesql @SQL

GO

------------------------------------------------------------------------------------------------------------------------
-- Stored procedure name: [dbo].[p_ACTP_EmployeeCertification_SelectAll]
-- Database: ASPNETVN.PORTAL
-- Author: Ngo Thanh Tung
-- Time created: Monday, October 04, 2010
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[p_ACTP_EmployeeCertification_SelectAll]
	@ModuleID UNIQUEIDENTIFIER
AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SELECT
	[ID],
	[ModuleID],
	[CertificationID],
	[EmployeeID],
	[CertifiedDate],
	[ExpiredDate],
	[CreatedBy],
	[CreatedDate],
	[UpdatedBy],
	[UpdatedDate]
FROM
	[dbo].[t_ACTP_EmployeeCertifications]	
WHERE 
	(ModuleID = @ModuleID)

GO

